#!/bin/bash

APP=edu
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

dwd_traffic_page_view_inc="
set hive.cbo.enable=false;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dwd_traffic_page_view_inc partition (dt = '$do_date')
select ods_log_inc.common.ar                                                           \`province_id\`,
       ods_log_inc.common.ba                                                           \`brand\`,
       ods_log_inc.common.ch                                                           \`channel\`,
       ods_log_inc.common.ch                                                           \`is_new\`,
       ods_log_inc.common.md                                                           \`model\`,
       ods_log_inc.common.mid                                                          \`mid_id\`,
       ods_log_inc.common.os                                                           \`operate_system\`,
       ods_log_inc.common.uid                                                          \`user_id\`,
       ods_log_inc.common.vc                                                           \`version_code\`,
       ods_log_inc.page.item                                                           \`page_item_id\`,
       ods_log_inc.page.item_type                                                      \`page_item_type\`,
       ods_log_inc.page.last_page_id                                                   \`last_page_id\`,
       ods_log_inc.page.page_id                                                        \`page_id\`,
       ods_log_inc.common.sc                                                           \`source_id\`,
       date_format(from_utc_timestamp(ods_log_inc.ts, 'GMT+8'), 'yyyy-MM-dd')          date_id,
       date_format(from_utc_timestamp(ods_log_inc.ts, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss') view_time,
       ods_log_inc.common.sid                                                          \`session_id\`,
       ods_log_inc.page.during_time                                                    \`during_time\`
from ods_log_inc
where dt = '$do_date'
  and page is not null;
"
dwd_traffic_action_inc="
insert overwrite table ${APP}.dwd_traffic_action_inc partition (dt = '$do_date')
select ods_log_inc.common.ar                                                      \`province_id\`,
       ods_log_inc.common.ba                                                      \`brand\`,
       ods_log_inc.common.ch                                                      \`channel\`,
       ods_log_inc.common.ch                                                      \`is_new\`,
       ods_log_inc.common.md                                                      \`model\`,
       ods_log_inc.common.mid                                                     \`mid_id\`,
       ods_log_inc.common.os                                                      \`operate_system\`,
       ods_log_inc.common.uid                                                     \`user_id\`,
       ods_log_inc.common.vc                                                      \`version_code\`,
       ods_log_inc.page.item                                                      \`page_item_id\`,
       ods_log_inc.page.item_type                                                 \`page_item_type\`,
       ods_log_inc.page.last_page_id                                              \`last_page_id\`,
       ods_log_inc.page.page_id                                                   \`page_id\`,
       ods_log_inc.common.sc                                                      \`source_id\`,
       action.action_id,
       action.item                                                                action_item,
       action.item_type                                                           action_item_tye,
       date_format(from_utc_timestamp(ods_log_inc.ts, 'GMT+8'), 'yyyy-MM-dd')     date_id,
       date_format(from_utc_timestamp(action.ts, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss') action_time
from ods_log_inc
         lateral view explode(ods_log_inc.actions) tmp as action
where dt = '$do_date'
  and actions is not null;
"
dwd_video_play_inc="
insert overwrite table ${APP}.dwd_video_play_inc partition (dt = '$do_date')
select a.province_id,
       a.brand,
       a.channel,
       a.is_new,
       a.model,
       a.mid_id,
       a.operate_system,
       a.user_id,
       a.version_code,
       a.source_id,
       a.date_id,
       a.play_sec,
       a.position_sec,
       a.video_id,
       b.chapter_id as chapter_id,
       b.chapter_name,
       c.id         as course_id,
       c.course_name
from (
         select common.ar                                                  \`province_id\`,
                common.ba                                                  \`brand\`,
                common.ch                                                  \`channel\`,
                common.ch                                                  \`is_new\`,
                common.md                                                  \`model\`,
                common.mid                                                 \`mid_id\`,
                common.os                                                  \`operate_system\`,
                common.uid                                                 \`user_id\`,
                common.vc                                                  \`version_code\`,
                common.sc                                                  \`source_id\`,
                date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd') \`date_id\`,
                appVideo.play_sec,
                appVideo.position_sec,
                appVideo.video_id
         from ods_log_inc
         where dt = '$do_date'
           and appVideo is not null
     ) a
         join dim_chapter_full b on a.video_id = b.video_id
         join dim_course_full c on b.course_id = c.id
where b.dt = '$do_date'
  and c.dt = '$do_date';
"
dwd_trade_cart_add_inc="
with cart_inc as (
    select data.id,
           data.user_id,
           data.course_id,
           data.course_name,
           date_format(data.create_time, 'yyyy-MM-dd') date_id,
           data.create_time
    from edu.ods_cart_info_inc
    where dt = '$do_date'
      and type = 'insert')
insert overwrite table ${APP}.dwd_trade_cart_add_inc
partition
(
dt
)
select cart_inc.id,
       user_id,
       course_id,
       cart_inc.course_name,
       date_id,
       cart_inc.create_time,
       subject_id,
       chapter_num,
       origin_price,
       reduce_amount,
       actual_price,
       date_format(cart_inc.create_time, 'yyyy-MM-dd') dt
from cart_inc
         join dim_course_full on cart_inc.course_id = dim_course_full.id
where dim_course_full.dt = '$do_date';
"
dwd_trade_order_detail_inc="
with
od as(
select
    data.course_id,
    data.course_name,
    data.order_id,
    data.user_id
from ods_order_detail_inc
where dt = '$do_date'
and type = 'insert'
),
oi as(
select
    data.id,
    data.user_id,
    data.origin_amount,
    data.coupon_reduce,
    data.final_amount,
    data.order_status,
    data.out_trade_no,
    data.trade_body,
    data.session_id,
    data.province_id,
    data.create_time,
    data.expire_time,
    data.update_time
from ods_order_info_inc
where dt = '$do_date'
and type = 'insert'
 ),
pro as(
select
    id,
    name,
    region_id,
    area_code,
    iso_code,
    iso_3166_2
from ods_base_province_full
where dt = '$do_date'
 )
insert overwrite table ${APP}.dwd_trade_order_detail_inc partition(dt = '$do_date')
select
    oi.id,
    course_id,
    course_name,
    order_id,
    oi.user_id,
    origin_amount,
    coupon_reduce,
    final_amount,
    session_id,
    create_time,
    update_time,
    order_status,
    out_trade_no,
    trade_body,
    province_id,
    expire_time,
    name,
    region_id,
    area_code,
    iso_code,
    iso_3166_2
from oi
left join od on oi.id = od.order_id
left join pro on oi.province_id = pro.id;
"
dwd_trade_pay_detail_suc_inc="
with
pay as
 (
select
    data.id,
    data.out_trade_no,
    data.order_id,
    data.alipay_trade_no,
    data.total_amount,
    data.trade_body,
    data.payment_type,
    data.payment_status,
    data.create_time,
    data.update_time,
    data.callback_content,
    data.callback_time
from ods_payment_info_inc
where dt = '$do_date'
and type = 'insert'
 ),
od as(
select
    data.order_id,
    data.course_id,
    data.course_name,
    data.user_id,
    data.origin_amount,
    data.coupon_reduce,
    data.final_amount
from ods_order_detail_inc
where (dt = '$do_date' or dt = date_add('$do_date', -1))
and (type = 'insert' and type = 'bootstrap-insert')
)
insert overwrite table ${APP}.dwd_trade_pay_detail_suc_inc partition(dt = '$do_date')
select
    id,
    out_trade_no,
    pay.order_id,
    alipay_trade_no,
    total_amount,
    trade_body,
    payment_type,
    payment_status,
    create_time,
    update_time,
    callback_content,
    callback_time,
    course_id,
    course_name,
    user_id,
    origin_amount,
    coupon_reduce,
    final_amount
from pay
left join od on pay.order_id = od.order_id;
"
dwd_trade_cart_full="
insert overwrite table ${APP}.dwd_trade_cart_full partition (dt = date_add('$do_date', -1))
select
    id,
    user_id,
    course_id,
    cart_price,
    session_id,
    create_time,
    update_time,
    deleted,
    sold
from ods_cart_info_full
where dt = date_add('$do_date', -1)
and sold = '0';
"
dwd_traffic_display_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_display_inc partition(dt='$do_date')
select
    \`ar\`,
    \`ba\`,
    \`ch\`,
    \`is_new\`,
    \`md\`,
    \`mid\`,
    \`os\`,
    \`sc\`,
    \`sid\`,
    \`uid\`,
    \`vc\`,
    \`during_time\`,
    \`page_item\`,
    \`page_item_type\`,
    \`last_page_id\`,
    \`page_id\`,
    \`display_type\`,
    \`display_item\`,
    \`display_item_type\`,
    \`order\`,
    \`pos_id\`,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') display_time
from
(
    select
        common.\`ar\`,
        common.\`ba\`,
        common.\`ch\`,
        common.\`is_new\`,
        common.\`md\`,
        common.\`mid\`,
        common.\`os\`,
        common.\`sc\`,
        common.\`sid\`,
        common.\`uid\`,
        common.\`vc\`,
        page.\`during_time\`,
        page.\`item\` page_item,
        page.\`item_type\` page_item_type,
        page.\`last_page_id\`,
        page.\`page_id\`,
        display.\`display_type\`,
        display.\`item\` display_item,
        display.\`item_type\` display_item_type,
        display.\`order\`,
        display.\`pos_id\`,
        \`ts\`
    from ods_log_inc lateral view explode(displays) tmp as display
    where dt='$do_date'
    and displays is not null
)log
left join
(
    select
        id province_id,
        area_code
    from ods_base_province_full
    where dt='$do_date'
)bp
on log.\`ar\`=bp.area_code;
"
dwd_traffic_start_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_start_inc partition(dt='$do_date')
select
    \`ar\`,
    \`ba\`,
    \`ch\`,
    \`is_new\`,
    \`md\`,
    \`mid\`,
    \`os\`,
    \`sc\`,
    \`sid\`,
    \`uid\`,
    \`vc\`,
    \`entry\`,
    \`first_open\`,
    \`loading_time\`,
    \`open_ad_id\`,
    \`open_ad_ms\`,
    \`open_ad_skip_ms\`,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') start_time
from
(
    select
        common.\`ar\`,
        common.\`ba\`,
        common.\`ch\`,
        common.\`is_new\`,
        common.\`md\`,
        common.\`mid\`,
        common.\`os\`,
        common.\`sc\`,
        common.\`sid\`,
        common.\`uid\`,
        common.\`vc\`,
        ts,
        \`start\`.entry,
        \`start\`.first_open,
        \`start\`.loading_time,
        \`start\`.open_ad_id,
        \`start\`.open_ad_ms,
        \`start\`.open_ad_skip_ms
    from ods_log_inc
    where dt='$do_date'
    and \`start\` is not null
)log
left join
(
    select
        id province_id,
        area_code
    from ods_base_province_full
    where dt='$do_date'
)bp
on log.\`ar\`=bp.area_code;
"
dwd_traffic_error_inc="
set hive.cbo.enable=false;
set hive.auto.convert.join=false;
insert overwrite table ${APP}.dwd_traffic_error_inc partition(dt='$do_date')
select
    \`ar\`,
    \`ba\`,
    \`ch\`,
    \`is_new\`,
    \`md\`,
    \`mid\`,
    \`os\`,
    \`sc\`,
    \`sid\`,
    \`uid\`,
    \`vc\`,
    \`page_item\`,
    \`page_item_type\`,
    \`last_page_id\`,
    \`page_id\`,
    \`entry\`,
    \`first_open\`,
    \`loading_time\`,
    \`open_ad_id\`,
    \`open_ad_ms\`,
    \`open_ad_skip_ms\`,
    \`actions\`,
    \`displays\`,
    \`appvideo\`,
    \`error_code\`,
    \`msg\`,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') error_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') error_time

from
(
    select
        common.\`ar\`,
        common.\`ba\`,
        common.\`ch\`,
        common.\`is_new\`,
        common.\`md\`,
        common.\`mid\`,
        common.\`os\`,
        common.\`sc\`,
        common.\`sid\`,
        common.\`uid\`,
        common.\`vc\`,
        page.\`item\` page_item,
        page.\`item_type\` page_item_type,
        page.\`last_page_id\`,
        page.\`page_id\`,
        \`start\`.entry,
        \`start\`.first_open,
        \`start\`.loading_time,
        \`start\`.open_ad_id,
        \`start\`.open_ad_ms,
        \`start\`.open_ad_skip_ms,
        \`ts\`,
        actions,
        displays,
        appvideo,
        err.\`error_code\`,
        err.\`msg\`
    from ods_log_inc
    where dt='$do_date'
    and err is not null
)log
left join
(
    select
        id province_id,
        area_code
    from ods_base_province_full
    where dt='$do_date'
)bp
on log.\`ar\`=bp.area_code;
set hive.auto.convert.join=true;
"
dwd_video_course_audition_inc="
insert overwrite table ${APP}.dwd_video_course_audition_inc partition (dt='$do_date')
select
       t2.user_id,
       t2.video_id,
       t2.chapter_id,
       t2.is_first,
       t2.date_id,
       t2.course_id,
       t3.course_name,
       t3.subject_id,
       t3.subject_name,
       t3.category_id,
       t3.category_name
from (
         select
                user_id,
                video_id,
                course_id,
                chapter_id,
                if(play_sec = position_sec, 1, 0) is_first,
                date_id
         from (
                  select user_id,
                         video_id,
                         play_sec,
                         position_sec,
                         date_id,
                         course_id,
                         chapter_id,
                         row_number() over (partition by user_id,video_id order by position_sec) rt
                  from dwd_video_play_inc
                  where dt = '$do_date'
              ) t1
         where rt = 1
     ) t2
left join (
    select
           id ,
           course_name,
           subject_id,
           subject_name,
           category_id,
           category_name
    from
    dim_course_full
    where dt='$do_date'
) t3 on t2.course_id=t3.id;

"
dwd_user_register_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dwd_user_register_inc partition(dt)
select
    ui.id user_id,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    channel,
    province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system,
    date_format(create_time,'yyyy-MM-dd') dt
from
(
    select
        data.id,
        data.create_time
    from ods_user_info_inc
    where dt='$do_date'
    and type='insert'
)ui
left join
(
    select
        user_id,
        collect_set(area_code)[0] area_code,
        collect_set(brand)[0] brand,
        collect_set(channel)[0] channel,
        collect_set(model)[0] model,
        collect_set(mid_id)[0] mid_id,
        collect_set(operate_system)[0] operate_system,
        collect_set(version_code)[0] version_code
    from
    (
        select
            common.ar area_code,
            common.ba brand,
            common.ch channel,
            common.md model,
            common.mid mid_id,
            common.os operate_system,
            common.uid user_id,
            common.vc version_code
        from ods_log_inc
        where dt='$do_date'
        and page is not null
        and common.uid is not null
    )t1
    group by user_id
)log
on ui.id=log.user_id
left join
(
    select
        id province_id
    from ods_base_province_full
    where dt='$do_date'
)pro
on log.area_code=pro.province_id;
"
dwd_user_login_inc="
insert overwrite table ${APP}.dwd_user_login_inc partition(dt='$do_date')
select
    user_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') login_time,
    channel,
    first_login,
    province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system,
    source,
    session,
    row_number() over (partition by session order by ts) rs
from
(
    select
        area_code,
        brand,
        channel,
        first_login,
        model,
        mid_id,
        operate_system,
        source,
        session,
        user_id,
        version_code,
        ts
    from
    (
        select
            area_code,
            brand,
            channel,
            first_login,
            model,
            mid_id,
            operate_system,
            source,
            session,
            user_id,
            version_code,
            ts,
            session_id,
            row_number() over (partition by session_id order by ts) rn
        from
        (
            select
                area_code,
                brand,
                channel,
                first_login,
                model,
                mid_id,
                operate_system,
                source,
                session,
                user_id,
                version_code,
                ts,
                concat(mid_id,'_',last_value(session_id,true) over(partition by mid_id order by ts)) session_id
            from
            (
                select
                    common.ar area_code,
                    common.ba brand,
                    common.ch channel,
                    common.is_new first_login,
                    common.md model,
                    common.mid mid_id,
                    common.os operate_system,
                    common.sc source,
                    common.sid session,
                    common.uid user_id,
                    common.vc version_code,
                    ts,
                    if(page.last_page_id is null,ts,null) session_id
                from ods_log_inc
                where dt='$do_date'
                and page is not null
            )t1
        )t2
        where t2.user_id is not null
    )t3
    where t3.rn=1
)log
left join
(
    select
        id province_id
    from ods_base_province_full
    where dt='$do_date'
)pro
on log.area_code=pro.province_id;
"
dwd_interaction_review_inc="
insert overwrite table ${APP}.dwd_interaction_review_inc partition(dt = '$do_date')
select
        data.id,
        data.user_id,
        data.course_id,
        data.create_time,
        data.review_stars,
        data.review_txt
from ods_review_info_inc
where dt='$do_date'
and type='insert';
"
dwd_interaction_comment_inc="
with
com as(
    select
        data.id,
        data.user_id,
        data.chapter_id,
        data.create_time,
        data.comment_txt
    from ods_comment_info_inc
    where dt='$do_date'
    and type='insert'
),
ord as(
    select
        data.id order_id,
        data.user_id
    from ods_order_info_inc
    where dt='$do_date'
)
insert overwrite table ${APP}.dwd_interaction_comment_inc partition(dt = '$do_date')
select
    id,
    com.user_id,
    chapter_id,
    order_id,
    date_format(create_time, 'yyyy-MM-dd') create_time,
    comment_txt
from com
left join
ord on com.user_id=ord.user_id;
"
dwd_study_test_inc="
with
test as (
    select
        data.id,
        data.user_id,
        data.paper_id,
        data.score,
        data.duration_sec,
        data.create_time
    from ods_test_exam_inc
    where dt='$do_date'
    and type='insert'
),
paper as (
    select
        id p_id,
        paper_title,
        course_id
    from ods_test_paper_full
    where dt='$do_date'
),
course as(
select
        id c_id,
        course_name
    from ods_course_info_full
    where dt='$do_date'
)
insert overwrite table ${APP}.dwd_study_test_inc partition(dt)
select
    id,
    user_id,
    paper_title,
    paper_id,
    course_name,
    course_id,
    score,
    duration_sec,
    create_time,
    date_format(create_time, 'yyyy-MM-dd') dt
from
test
left join paper on paper_id = paper.p_id
left join course on course_id = course.c_id;
"
dwd_study_question_inc="
insert overwrite table ${APP}.dwd_study_question_inc partition (dt = '$do_date')
select
       data.id,
       data.question_id,
       data.user_id,
       data.is_correct,
       data.score
from ods_test_exam_question_inc
where dt = '$do_date'
and type='insert';
"

case $1 in
    "dwd_traffic_page_view_inc" )
            hive -e "$dwd_traffic_page_view_inc"
    ;;
    "dwd_traffic_action_inc" )
            hive -e "$dwd_traffic_action_inc"
    ;;
    "dwd_video_play_inc" )
            hive -e "$dwd_video_play_inc"
    ;;
    "dwd_trade_cart_add_inc" )
            hive -e "$dwd_trade_cart_add_inc"
    ;;
    "dwd_trade_order_detail_inc" )
            hive -e "$dwd_trade_order_detail_inc"
    ;;
    "dwd_trade_pay_detail_su_inc" )
            hive -e "$dwd_trade_pay_detail_su_inc"
    ;;
    "dwd_trade_cart_full" )
            hive -e "$dwd_trade_cart_full"
    ;;
    "dwd_traffic_display_inc" )
            hive -e "$dwd_traffic_display_inc"
    ;;
    "dwd_traffic_start_inc" )
            hive -e "$dwd_traffic_start_inc"
    ;;
    "dwd_traffic_error_inc" )
            hive -e "$dwd_traffic_error_inc"
    ;;
    "dwd_video_course_audition_inc" )
            hive -e "$dwd_video_course_audition_inc"
    ;;
    "dwd_user_register_inc" )
            hive -e "$dwd_user_register_inc"
    ;;
    "dwd_user_login_inc" )
            hive -e "$dwd_user_login_inc"
    ;;
    "dwd_interaction_review_inc" )
            hive -e "$dwd_interaction_review_inc"
    ;;
    "dwd_interaction_comment_inc" )
            hive -e "$dwd_interaction_comment_inc"
    ;;
    "dwd_study_test_inc" )
            hive -e "$dwd_study_test_inc"
    ;;
    "dwd_study_question_inc" )
        hive -e "$dwd_study_question_inc"
    ;;
    "all" )
        hive -e "$dwd_traffic_page_view_inc$dwd_traffic_action_inc$dwd_video_play_inc$dwd_trade_cart_add_inc$dwd_trade_order_detail_inc$dwd_trade_pay_detail_su_inc$dwd_trade_cart_full$dwd_traffic_display_inc$dwd_traffic_start_inc$dwd_traffic_error_inc$dwd_video_course_audition_inc$dwd_user_register_inc$dwd_user_login_inc$dwd_interaction_review_inc$dwd_interaction_comment_inc$dwd_study_test_inc$dwd_study_question_inc";;
esac